﻿using Business.Core.Annotations;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using FangPage.Excel;
using FangPage.Common;
using System.IO;
using System.Web;
using Common;

namespace ProjectManagerSys
{
    /// <summary>
    /// 任务导出
    /// </summary>
    public class TaskExportMerber : Common.BusinessBase
    {
        #region 导出

        [Doc("任务统计导出", Group = "任务导出")]
        public virtual async Task<dynamic> ExportStatis()
        {
            //客户端保存的文件名
            string fileName =DateTime.Now.Date.ToString("yyyy-MM-dd") + "-员工任务统计.xls";
            try
            {
                HSSFWorkbook hSSFWorkbook = CreateStatisExcel();
                using (MemoryStream memoryStream = new MemoryStream())
                {
                   
                    hSSFWorkbook.Write(memoryStream);
                    HttpContext.Current.Response.ContentType = ("application/vnd.ms-excel");
                    HttpContext.Current.Response.Headers.Add("Content-Disposition", "attachment;filename=" + FPUtils.UrlEncode(fileName));
                    HttpContext.Current.Response.Body.Write(memoryStream.GetBuffer());
                    memoryStream.Close();
                    memoryStream.Dispose();
                }
            }
            catch (Exception ex) { }
            
            return null;
        }

        /// <summary>
        /// 构建任务统计Excel表格
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private HSSFWorkbook CreateStatisExcel()
        {
            byte[] dataExcel = null;
            HSSFWorkbook book = new HSSFWorkbook();
            using (System.IO.MemoryStream msExcel = new System.IO.MemoryStream())
            {
                ISheet sheet = book.CreateSheet("员工任务统计");
                sheet.DefaultColumnWidth = 15;

                //样式
                ICellStyle title_style = GetCellStyle(book, "title");

                ICellStyle th_style = GetCellStyle(book, "fonthead");

                ICellStyle cellstyle = GetCellStyle(book, "");

                ICellStyle is_style = GetCellStyle(book, "is_num");

                //标题
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
                NPOI.SS.UserModel.IRow rowt = sheet.CreateRow(0);
                rowt.Height = 56 * 10;
                var title0 = rowt.CreateCell(0);
                title0.SetCellValue("员工任务统计");
                title0.CellStyle = title_style;

                //表头
                NPOI.SS.UserModel.IRow rowh = sheet.CreateRow(1);
                rowh.Height = 60 * 10;
                SetCellValue(rowh, 0, "姓名", th_style);
                sheet.SetColumnWidth(0, 20 * 256 + 200);//列宽
                //sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));//单元格合并（起始行号，终止行号， 起始列号，终止列号）
                SetCellValue(rowh, 1, "任务总数", th_style);
                SetCellValue(rowh, 2, "未完成", th_style);
                SetCellValue(rowh, 3, "已完成", th_style);
                SetCellValue(rowh, 4, "提前完成", th_style);
                SetCellValue(rowh, 5, "逾期完成", th_style);
                SetCellValue(rowh, 6, "个人能力(%)", th_style);
                SetCellValue(rowh, 7, "饱和度", th_style);

                const int contentRow = 2;//内容起始行Index
                var dt = new IndexMerber().UsersStatis2(); //任务统计
                var perSat = new IndexMerber().CalculatePersonSat2(); //个人能力

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow rr = sheet.CreateRow(i + contentRow);
                    rr.Height = 50 * 10;
                    SetCellValue(rr, 0, dt.Rows[i]["realname"].ToString(), cellstyle);
                    SetCellValue(rr, 1, dt.Rows[i]["alltotal"].ToString(), cellstyle);
                    SetCellValue(rr, 2, dt.Rows[i]["taskno"].ToString(), cellstyle);
                    SetCellValue(rr, 3, dt.Rows[i]["finishtask"].ToString(), cellstyle);
                    SetCellValue(rr, 4, dt.Rows[i]["taskup"].ToString(), cellstyle);
                    SetCellValue(rr, 5, dt.Rows[i]["overtask"].ToString(), GetCellStyle(book, "red"));
                    SetCellValue(rr, 6, Math.Round((decimal)dt.Rows[i]["uppercent"]).ToString(), cellstyle);
                    var sat = "0";
                    if (((decimal)dt.Rows[i]["totaltimes"] + (decimal)dt.Rows[i]["overtimes"]) > 0)
                    { //分子大于0
                        foreach (DataRow item in perSat.Rows)
                        {
                            if (dt.Rows[i]["id"] == item["id"])
                            {
                                if ((decimal)item["alltimes"] > 0)
                                { //分母大于0
                                    sat = (Math.Round((((decimal)dt.Rows[i]["totaltimes"] + (decimal)dt.Rows[i]["overtimes"]) / (decimal)item["alltimes"]) * 100) / 100).ToString();
                                }
                            }
                        }
                    }
                    
                    SetCellValue(rr, 7, sat, cellstyle);
                }

                book.Write(msExcel);
                msExcel.Position = 0;
                dataExcel = msExcel.ToArray();
            }
            return book;
        }
        #endregion

        #region 获取样式
        /// <summary>
        /// 获取样式
        /// </summary>
        /// <param name="book"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public ICellStyle GetCellStyle(HSSFWorkbook book, string name)
        {
            ICellStyle style = book.CreateCellStyle();
            IFont font = book.CreateFont();
            switch (name)
            {
                case "title"://标题
                    {

                        font.IsBold = true;//粗体
                        font.FontHeightInPoints = 16;
                        style.SetFont(font);
                    }
                    break;
                case "ctable"://制表
                    {

                    }
                    break;
                case "fonthead"://表头的字体
                    {
                        font.IsBold = true;
                        style.SetFont(font);
                    }
                    break;
                case "greyhead"://灰色表头
                    {
                        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                        style.WrapText = true;
                    }
                    break;
                case "bluehead"://蓝色表头
                    {
                        style.FillForegroundColor = 40;// NPOI.HSSF.Util.HSSFColor.AQUA.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                    }
                    break;
                case "headch"://子表头
                    {
                        style.FillForegroundColor = 42;//NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                    }
                    break;
                case "is_num"://是否或序号
                    {
                        style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;//单元格背景色
                        style.FillPattern = FillPattern.SolidForeground;
                    }
                    break;
                case "red"://设置红色字体
                    {
                        font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;//设置文字颜色
                        style.SetFont(font);
                    }
                    break;
                default://单元格
                    {
                        style.VerticalAlignment = VerticalAlignment.Center;
                        style.Alignment = HorizontalAlignment.Center;
                        style.WrapText = true;
                    }
                    break;
            }
            //上下左右边框
            /*style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;*/
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
            style.Alignment = HorizontalAlignment.Center;//水平居中
            return style;
        }
        #endregion

        #region 设置列的值
        /// <summary>
        /// 设置列的值
        /// </summary>
        /// <param name="rr">IRow行对象</param>
        /// <param name="cellIndex">列Index</param>
        /// <param name="value">值</param>
        /// <param name="style">样式</param>
        public void SetCellValue(IRow rr, int cellIndex, string value, ICellStyle style)
        {
            var cell = rr.CreateCell(cellIndex);
            cell.SetCellValue(value);
            cell.CellStyle = style;
        }
        #endregion
    }
}
